package net.zjitc.dao;

import com.mchange.v2.c3p0.impl.DefaultConnectionTester;
import com.mchange.v2.c3p0.impl.NewProxyResultSet;
import net.zjitc.domain.User;
import net.zjitc.utils.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.management.Query;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/18.
 */
public class UserDao {
    public int pageSize=3;
    public List<User> findPage(int page) throws SQLException {
        //连接数据库C3p0
        Connection connection= C3P0Utils.getConnertion();
        //sql
        String sql="select * from user order by id limit ?,?";
        PreparedStatement ps=connection.prepareStatement(sql);
        ps.setInt(1,(page-1)*pageSize);
        ps.setInt(2,pageSize);
        ResultSet result=ps.executeQuery();
        //使用集合，存放查出来的对象
        List<User> list=new ArrayList<>();
        while (result.next()) {
                User user = new User();
                user.setId(result.getInt(1));
                user.setUername(result.getString(2));//获得第二列的值
                user.setPassword(result.getString(3));//获得第三列的值
                list.add(user);
            }
        return list;
    };
    public List<User> findAll() throws SQLException {
        //连接数据库C3p0
        Connection connection= C3P0Utils.getConnertion();
        //sql
        String sql="select * from user";
        PreparedStatement ps=connection.prepareStatement(sql);
        ResultSet result=ps.executeQuery();
        //使用集合，存放查出来的对象
        List<User> list=new ArrayList<>();
        while (result.next()) {
            User user = new User();
            user.setId(result.getInt(1));
            user.setUername(result.getString(2));
            user.setPassword(result.getString(3));
            list.add(user);
        }
        return list;
    }
    public List<User> findAllByUtils() throws SQLException {
        //执行SQL的对象，参数为连接池，是DataSource类或者其子类
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSources());
        String sql="select * from user";
        //使用封装策略自动封装
        //结果集的封装策略：ResultSetHandle类
        //因为结果有不同的各类，可能是个集合，也可能是个对象
        //所以使用ResultSetHandle类的子类，封装集合的是BeanListHandler，而封闭一个对象的BeanHandler子类
        //User.class是封装的参考模型，javaBean
        List list =(List) runner.query(sql,new BeanListHandler(User.class));
        return list;
    }




    /**
     * 按id查询：注意结果集的封装，BeanListHandler？？BeanHandler？
     * */
    public User findById(int id) throws SQLException {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSources());
        String sql="select * from user where id=?";
        User user=(User)runner.query(sql,new BeanHandler(User.class),id);
        return  user;
    }
}

